
import pandas as pd
from math import radians, sin, cos, sqrt, atan2


def calculate_distance(lat1, lon1, lat2, lon2):
    # 地球平均半径，单位为米
    R = 6371000
    lat1_rad = radians(lat1)
    lon1_rad = radians(lon1)
    lat2_rad = radians(lat2)
    lon2_rad = radians(lon2)

    dlat = lat2_rad - lat1_rad
    dlon = lon2_rad - lon1_rad

    a = sin(dlat / 2)**2 + cos(lat1_rad) * cos(lat2_rad) * sin(dlon / 2)**2
    c = 2 * atan2(sqrt(a), sqrt(1 - a))

    distance = R * c
    return distance


# 假设Excel文件路径和sheet名称如下
file_path = r'D:\xlzx\副本3月联通反馈电信经纬度偏差问题小区20250310.xlsx'  # 更新为新的文件目录
sheet1_name = '4GRRU'  # 替换为实际的sheet名称
sheet2_name = '5GAAU'  # 替换为实际的sheet名称

# 读取Excel文件中的数据
df1 = pd.read_excel(file_path, sheet_name=sheet1_name)
df2 = pd.read_excel(file_path, sheet_name=sheet2_name)

# 计算 df1 的距离
distances_df1 = []
skipped_indices_df1 = []  # 用于记录跳过的行索引

for index, row in df1.iterrows():
    try:
        # 尝试将经纬度列转换为浮点数
        lat1 = float(row['RRU经度(WGS84)'])
        lon1 = float(row['RRU纬度(WGS84)'])
        lat2 = float(row['RRU经度(铁塔)'])  # 修改为同一行的铁塔坐标
        lon2 = float(row['RRU纬度(铁塔)'])  # 修改为同一行的铁塔坐标
    except ValueError:
        # 如果转换失败，记录索引并跳过该行
        skipped_indices_df1.append(index)
        continue

    # 计算距离
    distance = calculate_distance(lat1, lon1, lat2, lon2)
    distances_df1.append(distance)

df1['Distance'] = distances_df1

# 计算 df2 的距离
distances_df2 = []
skipped_indices_df2 = []  # 用于记录跳过的行索引

for index, row in df2.iterrows():
    try:
        # 尝试将经纬度列转换为浮点数
        lat1 = float(row['AAU经度(铁塔)'])
        lon1 = float(row['AAU纬度(铁塔)'])
        lat2 = float(row['AAU经度(WGS84)'])  # 修改为同一行的WGS84坐标
        lon2 = float(row['AAU纬度(WGS84)'])  # 修改为同一行的WGS84坐标
    except ValueError:
        # 如果转换失败，记录索引并跳过该行
        skipped_indices_df2.append(index)
        continue

    # 计算距离
    distance = calculate_distance(lat1, lon1, lat2, lon2)
    distances_df2.append(distance)

df2['Distance'] = distances_df2

# 将结果写入新的Excel文件
new_file_path = r'D:\xlzx\副本3月联通反馈电信经纬度偏差问题小区20250310_结果.xlsx'  # 新文件路径
with pd.ExcelWriter(new_file_path, engine='openpyxl') as writer:
    df1.to_excel(writer, sheet_name=sheet1_name, index=False)
    df2.to_excel(writer, sheet_name=sheet2_name, index=False)  # 保存第二个sheet的结果
